﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
   public class ProductDAL:SuperDataBase,IProductDAL
    {
       public DataTable GetProductsByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
            strCommandText.AppendFormat(
                   "SELECT row_number() over({0}) AS rownum, ProductId, ProductName, ProductCode,ProductModel,ProductUnit,ProductSize,PackingSize,", strOrderBy);
            strCommandText.Append(" [Thumbnail1],Image1,PackStandard1,PackStandard2,PackStandard3,IsDisabled FROM T_BasicData_Product ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));
          
           return ds.Tables[0];
       }

       public void Delete(int productId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("delete T_BasicData_Product  ");
           strSql.Append(" where ProductId=@productId");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@productId", DbType.Int32, productId);
           DB.ExecuteNonQuery(cmd);
       }

       public Product GetProductById(int productId)
       {
           Product model = null;
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" select  top 1 ProductId,ProductName,ProductCode,ProductModel,ProductUnit,ProductSize,");
           strSql.Append(" PackingSize,UnitVolume,tbp.Description,Source,IsDetail,Image1,Image2,Image3,Image4,Thumbnail1,");
           strSql.Append(" Thumbnail2,Thumbnail3,Thumbnail4,PackStandard1,PackStandard2,PackStandard3,BrandName,tbpy.Name as ProductTypeName,tbp.brandId,tbp.ProductTypeId ");
           strSql.Append(" from T_BasicData_Product as tbp left join T_BasicData_ProductType as tbpy ");
           strSql.Append(" on tbp.ProductTypeId=tbpy.Id ");
           strSql.Append("  left join T_BasicData_Brand as tbb on tbpy.BrandId=tbb.BrandId  ");
           strSql.Append(" where ProductId=@productId");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@productId", DbType.Int32, productId);
           using (IDataReader dr = DB.ExecuteReader(cmd))
           {
               while (dr.Read())
               {
                   model = new Product();
                   int id=0;
                   int.TryParse(dr["ProductId"].ToString(), out productId);
                   model.ProductId = id;
                   model.ProductCode = dr["ProductCode"].ToString();
                   model.ProductName = dr["ProductName"].ToString();
                   model.ProductModel = dr["ProductModel"].ToString();
                   model.ProductUnit = dr["ProductUnit"].ToString();
                   model.ProductSize = dr["ProductSize"].ToString();
                   model.PackingSize = dr["PackingSize"].ToString();
                   model.UnitVolume = dr["UnitVolume"].ToString();
                   model.Desctipion = dr["Description"].ToString();
                   model.Source = dr["Source"].ToString();
                   model.IsDetail =Convert.ToBoolean(dr["IsDetail"].ToString());
                   model.Image1 =dr["Image1"].ToString();
                   model.Image2 = dr["Image2"].ToString();
                   model.Image3 = dr["Image3"].ToString();
                   model.Image4 = dr["Image4"].ToString();
                   model.Thumbnail1 = dr["Thumbnail1"].ToString();
                   model.Thumbnail2 = dr["Thumbnail2"].ToString();
                   model.Thumbnail3 = dr["Thumbnail3"].ToString();
                   model.Thumbnail4 = dr["Thumbnail4"].ToString();
                   model.BrandName = dr["BrandName"].ToString();
                   model.ProductTypeName = dr["ProductTypeName"].ToString();
                   model.BrandId = Convert.ToInt32(dr["BrandId"].ToString());
                   model.ProductTypeId = Convert.ToInt32(dr["ProductTypeId"].ToString());
                   model.PackStandard1 = dr["PackStandard1"].ToString();
                   model.PackStandard2 = dr["PackStandard2"].ToString();
                   model.PackStandard3 = dr["PackStandard3"].ToString();
               }
               
           }
           return model;
       }

       public void Add(Product product)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" insert into T_BasicData_Product (BrandId,ProductTypeId,ProductName,ProductCode,ProductModel,ProductUnit,ProductSize,");
           strSql.Append(" PackingSize,UnitVolume,Description,Source,IsDetail,Image1,Image2,Image3,Image4,Thumbnail1,");
           strSql.Append(" Thumbnail2,Thumbnail3,Thumbnail4,PackStandard1,PackStandard2,PackStandard3,IsDisabled) values(@brandId,@productTypeId,@productName,@productCode,@productModel,@productUnit,@productSize,");
           strSql.Append(" @packingSize,@unitVolume,@description,@source,@isDetail,@image1,@image2,@image3,@image4,@thumbnail1,@thumbnail2,");
           strSql.Append(" @thumbnail3,@thumbnail4,@PackStandard1,@PackStandard2,@PackStandard3,0)");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());

           DB.AddInParameter(cmd, "@brandId", DbType.Int32, product.BrandId);
           DB.AddInParameter(cmd, "@productTypeId", DbType.Int32, product.ProductTypeId);
           DB.AddInParameter(cmd, "@productName", DbType.String, product.ProductName);
           DB.AddInParameter(cmd, "@productCode", DbType.String, product.ProductCode);
           DB.AddInParameter(cmd, "@productModel", DbType.String, product.ProductModel);
           DB.AddInParameter(cmd, "@productUnit", DbType.String, product.ProductUnit);
           DB.AddInParameter(cmd, "@productSize", DbType.String, product.ProductSize);
           DB.AddInParameter(cmd, "@packingSize", DbType.String, product.PackingSize);
           DB.AddInParameter(cmd, "@unitVolume", DbType.String, product.UnitVolume);
           DB.AddInParameter(cmd, "@description", DbType.String, product.Desctipion);
           DB.AddInParameter(cmd, "@source", DbType.String, product.Source);
           DB.AddInParameter(cmd, "@isDetail", DbType.Boolean, product.IsDetail);
           DB.AddInParameter(cmd, "@image1", DbType.String, product.Image1);
           DB.AddInParameter(cmd, "@image2", DbType.String, product.Image2);
           DB.AddInParameter(cmd, "@image3", DbType.String, product.Image3);
           DB.AddInParameter(cmd, "@image4", DbType.String, product.Image4);
           DB.AddInParameter(cmd, "@thumbnail1", DbType.String, product.Thumbnail1);
           DB.AddInParameter(cmd, "@thumbnail2", DbType.String, product.Thumbnail2);
           DB.AddInParameter(cmd, "@thumbnail3", DbType.String, product.Thumbnail3);
           DB.AddInParameter(cmd, "@thumbnail4", DbType.String, product.Thumbnail4);
           DB.AddInParameter(cmd, "@PackStandard1", DbType.String, product.PackStandard1);
           DB.AddInParameter(cmd, "@PackStandard2", DbType.String, product.PackStandard2);
           DB.AddInParameter(cmd, "@PackStandard3", DbType.String, product.PackStandard3);
           DB.ExecuteNonQuery(cmd);
       }

       public void Update(Product product)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(
               " update T_BasicData_Product set BrandId=@brandId,ProductTypeId=@productTypeId,ProductName=@productName,ProductCode=@productCode,"); 
           strSql.Append(" ProductModel=@productModel,ProductUnit=@productUnit,ProductSize=@productSize,");
           strSql.Append(" PackingSize=@packingSize,UnitVolume=@unitVolume,Description=@description,Source=@source,");
           strSql.Append(" IsDetail=@isDetail,Image1=@image1,Image2=@image2,Image3=@image3,Image4=@image4,Thumbnail1=@thumbnail1,");
           strSql.Append(" Thumbnail2=@thumbnail2,Thumbnail3=@thumbnail3,Thumbnail4=@thumbnail4,");
           strSql.Append(" PackStandard1=@PackStandard1,PackStandard2=@PackStandard2,PackStandard3=@PackStandard3  where ProductId=@productId");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());

           DB.AddInParameter(cmd, "@brandId", DbType.Int32, product.BrandId);
           DB.AddInParameter(cmd, "@productTypeId", DbType.Int32, product.ProductTypeId);
           DB.AddInParameter(cmd, "@productName", DbType.String, product.ProductName);
           DB.AddInParameter(cmd, "@productCode", DbType.String, product.ProductCode);
           DB.AddInParameter(cmd, "@productModel", DbType.String, product.ProductModel);
           DB.AddInParameter(cmd, "@productUnit", DbType.String, product.ProductUnit);
           DB.AddInParameter(cmd, "@productSize", DbType.String, product.ProductSize);
           DB.AddInParameter(cmd, "@packingSize", DbType.String, product.PackingSize);
           DB.AddInParameter(cmd, "@unitVolume", DbType.String, product.UnitVolume);
           DB.AddInParameter(cmd, "@description", DbType.String, product.Desctipion);
           DB.AddInParameter(cmd, "@source", DbType.String, product.Source);
           DB.AddInParameter(cmd, "@isDetail", DbType.Boolean, product.IsDetail);
           DB.AddInParameter(cmd, "@image1", DbType.String, product.Image1);
           DB.AddInParameter(cmd, "@image2", DbType.String, product.Image2);
           DB.AddInParameter(cmd, "@image3", DbType.String, product.Image3);
           DB.AddInParameter(cmd, "@image4", DbType.String, product.Image4);
           DB.AddInParameter(cmd, "@thumbnail1", DbType.String, product.Thumbnail1);
           DB.AddInParameter(cmd, "@thumbnail2", DbType.String, product.Thumbnail2);
           DB.AddInParameter(cmd, "@thumbnail3", DbType.String, product.Thumbnail3);
           DB.AddInParameter(cmd, "@thumbnail4", DbType.String, product.Thumbnail4);
           DB.AddInParameter(cmd,"@productId",DbType.Int32,product.ProductId);
           DB.AddInParameter(cmd, "@PackStandard1", DbType.String, product.PackStandard1);
           DB.AddInParameter(cmd, "@PackStandard2", DbType.String, product.PackStandard2);
           DB.AddInParameter(cmd, "@PackStandard3", DbType.String, product.PackStandard3);
           DB.ExecuteNonQuery(cmd);
       }


       public DataTable Query(string strWhere)
       {
           
           string sql = "SELECT * FROM [T_BasicData_Product]";
           sql += strWhere;
           DataSet ds = DB.ExecuteDataSet(CommandType.Text, sql);
           return ds.Tables[0];
           
       }

       public DataTable GetPriceInfo(int custId, DateTime priceDate,string strWhere)
       {
           string sql =" SELECT tbdp.ProductId, tbdp.ProductName, tbdp.ProductUnit, tbdp.UnitVolume, a.Price, a.TaxRatio, a.TaxPrice,";
           sql += " a.SpecialType FROM T_BasicData_Product AS tbdp,(";
           sql +="    SELECT Unit, Price, TaxRatio, TaxPrice, PriceType,C.ProductId,StartDate, EndDate, SpecialType ";
           sql += "   FROM (SELECT MAX(Id) AS id, ProductId from T_Sales_PriceList AS tspl WHERE (@priceDate BETWEEN StartDate AND EndDate) ";
           sql += "     AND EXISTS (SELECT PolicyId FROM T_Sales_PriceCust AS tspc WHERE tspl.PolicyId = PolicyId";
           if(custId != 0) // 查询所有产品信息,add by zhouyc 20110627
               sql += " AND  CustId =@custId";
           sql += ") GROUP BY ProductId) as B";
           sql+=" ,T_Sales_PriceList AS C where B.id = C.Id )AS a where tbdp.ProductId = a.ProductId ";
           sql += strWhere;
           DbCommand cmd = DB.GetSqlStringCommand(sql);
           if (custId != 0)
               DB.AddInParameter(cmd, "@custId", DbType.Int32, custId);
           DB.AddInParameter(cmd, "@priceDate", DbType.DateTime, priceDate);
           DataSet ds = DB.ExecuteDataSet(cmd);
           return ds.Tables[0];
       }

       public DataTable GetProductsWithPriceByPage(string strWhere,string custId,string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           if (string.IsNullOrEmpty(custId))
               custId = "0";
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat(
                  "SELECT row_number() over({0}) AS rownum, tbp.ProductId, ProductName, ProductCode,ProductModel,ProductUnit,ProductSize,PackingSize,unitVolume,", strOrderBy);
           strCommandText.Append(" [Thumbnail1],Image1,productType.name as ProductTypeName,a.Price, a.TaxRatio, a.TaxPrice,a.SpecialType FROM T_BasicData_Product as tbp left join (");
           strCommandText.Append("  SELECT Unit, Price, TaxRatio, TaxPrice, PriceType,C.ProductId,StartDate, EndDate, SpecialType");
           strCommandText.Append("   FROM (SELECT MAX(Id) AS id, ProductId from T_Sales_PriceList AS tspl WHERE (getdate() BETWEEN StartDate AND EndDate) ");
           strCommandText.Append("     AND EXISTS (SELECT PolicyId FROM T_Sales_PriceCust AS tspc WHERE tspl.PolicyId = PolicyId AND CustId ="+custId+") ");
           strCommandText.Append(" GROUP BY ProductId) as B ,T_Sales_PriceList AS C where B.id = C.Id )AS a  on tbp.ProductId=a.ProductId ");
           strCommandText.Append(" left join (SELECT Id, Name FROM T_BasicData_ProductType) AS productType ON tbp.ProductTypeId = productType.Id");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public void Disable(int productId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("update T_BasicData_Product set IsDisabled=1  ");
           strSql.Append(" where ProductId=@productId");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@productId", DbType.Int32, productId);
           DB.ExecuteNonQuery(cmd);
       }

       public void Enable(int productId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("update T_BasicData_Product set IsDisabled=0  ");
           strSql.Append(" where ProductId=@productId");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@productId", DbType.Int32, productId);
           DB.ExecuteNonQuery(cmd);
       }
    }
}
